/*============================================================================
  File:    Simple Sample ADOMD ClientAccess.cs

  Summary: Simple Sample client access to browse databases 
           and execute simple MDX queries.

  Date:    November 2006
------------------------------------------------------------------------------
  This file is part of the Microsoft SQL Server Code Samples.

  Copyright (C) Microsoft Corporation.  All rights reserved.

  This source code is intended only as a supplement to Microsoft
  Development Tools and/or on-line documentation.  See these other
  materials for detailed information regarding Microsoft code samples.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using adoMdClient = Microsoft.AnalysisServices.AdomdClient;
using Microsoft.AnalysisServices.Viewers;
using Microsoft.AnalysisServices.Controls;



namespace Microsoft.Samples.SqlServer
{
    public partial class SimpleSampleADOMDClientAccess : Form
    {
        private adoMdClient.AdomdConnection cnxAdomd;
        private DataTable mdxResults=new DataTable("mdxResults");
        private adoMdClient.CellSet adomdCellSet = null;
        private string ServerName = "";
        private string SelectedDatabase = "";


        private enum COORDINATE_TYPE
        {
            MDACTION_COORDINATE_CUBE = 1,
            MDACTION_COORDINATE_DIMENSION = 2,
            MDACTION_COORDINATE_LEVEL = 3,
            MDACTION_COORDINATE_MEMBER = 4,
            MDACTION_COORDINATE_SET = 5,
            MDACTION_COORDINATE_CELL = 6
        }

        public SimpleSampleADOMDClientAccess()
        {
            InitializeComponent();
        }

        private void connect2server_Click(object sender, EventArgs e)
        {
            if ((cnxAdomd != null))
            {
                cnxAdomd.Close();
            }
            else
            {
                cnxAdomd = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection();
            }

            if (serverName.Text.Length == 0)
            {
                ServerName = "localhost";
                serverName.Text = ServerName;
            }
            else
            {
                ServerName = serverName.Text;
            }

            cnxAdomd.ConnectionString = "Data Source=" + ServerName;

            //Open connection to server and fill up with main objects names
            try
            {
                cnxAdomd.Open();
            }
            catch( Exception ex)
            {
                string errorMsg = string.Concat("Connection to '" , ServerName , "' server is not ready or connectivity is broken.\n\rFull text message received follows:\n\r" , ex.Message);
                MessageBox.Show(errorMsg, "Simple Sample ADOMD ClientAccess");
                return;
            }
            if(cnxAdomd.State == ConnectionState.Open)
            {
                SelectedDatabase = cnxAdomd.Database;
                databaseName.Text = SelectedDatabase;
                availableDatabases.Items.Clear();

                //List available databases
                DataSet catalogs = cnxAdomd.GetSchemaDataSet(adoMdClient.AdomdSchemaGuid.Catalogs, null);
                DataTable schemaTable = catalogs.Tables[0];
                foreach (DataRow row in schemaTable.Rows)
                {
                    availableDatabases.Items.Add(row["CATALOG_NAME"].ToString());
                    if (row["CATALOG_NAME"].ToString() == SelectedDatabase)
                    {
                        availableDatabases.SelectedIndex = availableDatabases.Items.IndexOf(SelectedDatabase);
                    }
                }

                availableDatabases.Enabled = true;


            }
            else
            {
                MessageBox.Show("Connection to '" + ServerName + "' server is not ready or is broken\n\r", "Simple Sample ADOMD ClientAccess" );
            }

        }


        private void fillCellSet_Click(object sender, EventArgs e)
        {
            if(!(cnxAdomd==null) && (cnxAdomd.State== ConnectionState.Open))
            {
                StringBuilder mdxQuery = new StringBuilder();
                mdxQuery.Append("SELECT ");
                mdxQuery.Append(axisStatement.Text);
                mdxQuery.Append(" FROM ");
                mdxQuery.Append(string.Concat("[", fromCube.SelectedItem.ToString(), "]"));
                if (whereStatement.Text.Length > 0)
                {
                    mdxQuery.Append(" WHERE ");
                    mdxQuery.Append(whereStatement.Text);
                }

                adoMdClient.AdomdCommand mdxCommand = cnxAdomd.CreateCommand();

                mdxCommand.CommandText = mdxQuery.ToString();
                adomdCellSet = mdxCommand.ExecuteCellSet();
                if (adomdCellSet.Axes.Count <= 2)
                {
                    //Clear DataGridView object for new mdx results
                    mdxResultsView.DataSource = null;
                    mdxResultsView.Rows.Clear();
                    mdxResultsView.Columns.Clear();
                    mdxResults.Clear();

                    //Add column headings from Axis0 positions
                    //  first column should be empty to allow room for row headings
                    DataColumn mdxResultsColumn;
                    // Create new DataColumn, set DataType, 
                    // ColumnName and add to DataTable.    
                    mdxResultsColumn = new DataColumn();
                    mdxResultsColumn.DataType = System.Type.GetType("System.String");
                    mdxResultsColumn.ColumnName = "";
                    mdxResultsColumn.ReadOnly = true;
                    mdxResultsColumn.Unique = false;
                    // Add the Column to the DataColumnCollection.
                    mdxResults.Columns.Add(mdxResultsColumn);

                    //adding column headings from Axis0, column types defined as string with the intention to use formatted cell values
                    int columns = adomdCellSet.Axes[0].Positions.Count;
                    for (int i = 0; i < columns; i++)
                    {
                        // Create new DataColumn, set DataType, 
                        // ColumnName and add to DataTable.    
                        mdxResultsColumn = new DataColumn();
                        mdxResultsColumn.DataType = System.Type.GetType("System.String");
                        mdxResultsColumn.ColumnName = adomdCellSet.Axes[0].Positions[i].Members[0].UniqueName;
                        mdxResultsColumn.ReadOnly = true;
                        mdxResultsColumn.Unique = false;
                        // Add the Column to the DataColumnCollection.
                        mdxResults.Columns.Add(mdxResultsColumn);
                    }

                    //number of rows can be obtained from Axis1.Positions.Count
                    // Data elementes are layed out row-wise for two dimensional cellsets
                    // cellElement[n] ==> resultSet[ n mod columns, n mod rows]
                    DataRow currentRow=null;
                    int rows = adomdCellSet.Axes[1].Positions.Count;
                    int cells = adomdCellSet.Cells.Count;
                    for (int i = 0; i < cells; i++)
                    {
                        if ((i % columns) == 0)
                        {
                            if (currentRow != null)
                            {
                                mdxResults.Rows.Add(currentRow);
                            }
                            currentRow = mdxResults.NewRow();
                            currentRow[0] = adomdCellSet.Axes[1].Positions[i / columns].Members[0].Caption;
                            currentRow[1] = adomdCellSet.Cells[i].FormattedValue;
                        }
                        else
                        {
                            currentRow[(i%columns)+1] = adomdCellSet.Cells[i].FormattedValue;
                        }
                    }
                    mdxResults.Rows.Add(currentRow);
                    mdxResultsView.DataSource = mdxResults;
                    mdxResultsView.Show();





                    adomdCellSet.Cells.Count.ToString();
                    #region reviewing Cells
                    foreach (adoMdClient.Cell currentCell in adomdCellSet.Cells)
                    {
                        String currentValue;
                        currentValue = currentCell.Value==null? null: currentCell.Value.ToString();
                        currentValue = currentCell.FormattedValue;
                        currentValue = currentCell.CellProperties.Count.ToString();
                        foreach (adoMdClient.CellProperty currentCellProperty in currentCell.CellProperties)
                        {
                            currentValue = currentCellProperty.Name + " = " +  (currentCellProperty.Value==null? "#null#": currentCellProperty.Value.ToString());
                            currentValue = currentCellProperty.Namespace;
                        }

                    }
                    #endregion

                    adomdCellSet.Axes.Count.ToString();
                    #region reviewing Axes
                    foreach (adoMdClient.Axis currentAxis in adomdCellSet.Axes)
                    {
                        String currentValue;
                        currentValue = currentAxis.Name;
                        currentValue = currentAxis.Positions.ToString();
                        currentValue = currentAxis.Positions.Count.ToString();
                        #region reviewing positions per Axis
                        foreach (adoMdClient.Position currentPosition in currentAxis.Positions)
                        {
                            currentValue = currentPosition.Members.Count.ToString();
                            currentValue = currentPosition.Ordinal.ToString();
                            #region reviewing members per Position per Axis
                            foreach (adoMdClient.Member currentMember in currentPosition.Members)
                            {
                                currentValue = currentMember.Caption;
                                currentValue = currentMember.ChildCount.ToString();
                                currentValue = currentMember.Description;
                                currentValue = currentMember.DrilledDown.ToString();
                                currentValue = currentMember.LevelDepth.ToString();
                                currentValue = currentMember.LevelName;
                                currentValue = currentMember.MemberProperties.ToString();
                                currentValue = currentMember.Name;
                                currentValue = currentMember.Properties.ToString();
                                currentValue = currentMember.ToString();
                                currentValue = currentMember.UniqueName;
                            }
                            #endregion

                        }
                        #endregion

                        currentValue = currentAxis.Set.ToString();
                        currentValue = currentAxis.Set.Tuples.Count.ToString();
                        #region reviewing tuples per Axis
                        foreach (adoMdClient.Tuple currentTuple in currentAxis.Set.Tuples)
                        {
                            currentValue = currentTuple.Members.Count.ToString();
                            currentValue = currentTuple.TupleOrdinal.ToString();
                            #region reviewing members per Tuple per Axis
                            foreach (adoMdClient.Member currentMember in currentTuple.Members)
                            {
                                currentValue = currentMember.Caption;
                                currentValue = currentMember.ChildCount.ToString();
                                currentValue = currentMember.Description;
                                currentValue = currentMember.DrilledDown.ToString();
                                currentValue = currentMember.LevelDepth.ToString();
                                currentValue = currentMember.LevelName;
                                currentValue = currentMember.MemberProperties.ToString();
                                currentValue = currentMember.Name;
                                currentValue = currentMember.Properties.ToString();
                                currentValue = currentMember.ToString();
                                currentValue = currentMember.UniqueName;
                            }
                            #endregion
                        }
                        #endregion
                        currentValue = currentAxis.ToString();
                    }
                    #endregion


                }
                else    //More than 2 Axes in results, this sample can't handle them
                {
                    MessageBox.Show("More than 2 Axes in results, this sample can't handle them\r\nReview MDX expression and adjust accordingly");
                }
            }
        }

        private void mdxResultsView_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            int columns = adomdCellSet.Axes[0].Positions.Count;
            int rows = adomdCellSet.Axes[1].Positions.Count;
            String columnSlicer = adomdCellSet.Axes[0].Positions[(e.ColumnIndex-1)].Members[0].UniqueName;
            String rowSlicer = adomdCellSet.Axes[1].Positions[e.RowIndex].Members[0].UniqueName;
            String mdxRestriction = "(" + columnSlicer + ", " + rowSlicer + ")" ;
            String cubeName = fromCube.SelectedItem.ToString();
            if (!string.IsNullOrEmpty(cubeName))
            {
                readActionsUsingADOMDClient("SimpleSample", cubeName, mdxRestriction, COORDINATE_TYPE.MDACTION_COORDINATE_CELL);
            }

        }

        private void readActionsUsingADOMDClient(String catalogName, String cubeName, String coordinate, COORDINATE_TYPE coordinateType)
        {
            adoMdClient.AdomdRestrictionCollection restrictions = new adoMdClient.AdomdRestrictionCollection();

            restrictions.Add("CATALOG_NAME", catalogName);

            restrictions.Add("CUBE_NAME", cubeName);

            restrictions.Add("COORDINATE", coordinate);

            restrictions.Add("COORDINATE_TYPE", (int)coordinateType);
            foreach (adoMdClient.AdomdRestriction restriction in restrictions)
            {
                Debug.WriteLine(restriction.Name + "=" + restriction.Value);
            }
            DataSet actionsDataSet = cnxAdomd.GetSchemaDataSet("MDSCHEMA_ACTIONS", restrictions);
            DataTable actionsTable = actionsDataSet.Tables[0];
            Debug.WriteLine("Table [" + actionsTable.TableName + "] has " + actionsTable.Rows.Count.ToString() + " rows");
            int j = 0;
            foreach (DataRow actionRow in actionsTable.Rows)
            {
                for (int i = 0; i < actionRow.ItemArray.Length; i++)
                {
                    Debug.WriteLine("[" + j.ToString().PadLeft(4) + "]\t" + actionsTable.Columns[i].ColumnName + ": " + actionRow.ItemArray[i].ToString());
                }
                j++;
            }
        }

        private string extractCubeNameFromMDX(string mdx)
        {
            if (mdx.IndexOf("from ", StringComparison.InvariantCultureIgnoreCase) > 0)
            {
                string cubeName = mdx;
                cubeName = cubeName.Replace('\t', ' ');
                cubeName = cubeName.Replace('\r', ' ');
                cubeName = cubeName.Replace('\n', ' ');
                cubeName = cubeName.Replace("  ", " ");
                cubeName = cubeName.Trim();
                cubeName = cubeName.ToLowerInvariant();
                cubeName = cubeName.Substring(cubeName.IndexOf("from ") + 4);
                cubeName = cubeName.Trim();
                if (cubeName.Substring(0, 1) == "[")
                {
                    cubeName = cubeName.Substring(0, cubeName.IndexOf("]") + 1);
                    cubeName = cubeName.Trim("[]".ToCharArray());
                }
                else
                {
                    cubeName = cubeName.Substring(0, cubeName.IndexOf(" "));
                    cubeName = cubeName.Trim();
                }

                return cubeName;
            }
            else
            {
                return null;
            }
        }

        private void fillTreeView(TreeView mainChildren, adoMdClient.AdomdConnection databaseConnection)
        {
            this.Cursor = Cursors.WaitCursor;
            availableObjects.Enabled = false;
            actionTabs.Enabled = false;

            mainChildren.Nodes.Clear();

            TreeNode currentDatabaseNode = mainChildren.Nodes.Add(databaseConnection.Database);
            TreeNode currentDatabaseDimensions = currentDatabaseNode.Nodes.Add("Dimensions");
            TreeNode currentDatabaseCubes = currentDatabaseNode.Nodes.Add("Cubes");
            TreeNode currentDatabaseMiningStructures = currentDatabaseNode.Nodes.Add("MiningStructures");
            TreeNode currentDatabaseUnknownType = null;
            
            foreach (adoMdClient.CubeDef cube in databaseConnection.Cubes)
            {
                switch (cube.Type)
                {
                    case Microsoft.AnalysisServices.AdomdClient.CubeType.Cube:
                        TreeNode currentCubeNode = currentDatabaseCubes.Nodes.Add(cube.Name);
                        TreeNode currentCubeStatistics = currentCubeNode.Nodes.Add("Statisctics");
                        TreeNode currentCubeDimensions = currentCubeNode.Nodes.Add("Dimensions");
                        TreeNode currentCubeMeasures = currentCubeNode.Nodes.Add("Measures");
                        TreeNode currentCubeKPIs = currentCubeNode.Nodes.Add("KPIs");

                        //Add cube name to MDX sample tab
                        fromCube.Items.Add(cube.Name);

                        //Statistics
                        #region Statisctics
                        currentCubeStatistics.Nodes.Add("Last Updated: " + cube.LastUpdated.ToString());
                        currentCubeStatistics.Nodes.Add("Last Processed: " + cube.LastProcessed.ToString());
                        currentCubeStatistics.Nodes.Add("Caption: " + cube.Caption);
                        currentCubeStatistics.Nodes.Add("Description: " + cube.Description);
                        currentCubeStatistics.Nodes.Add("Name: " + cube.Name);
                        foreach (adoMdClient.Property cubeProperty in cube.Properties)
                        {
                            currentCubeStatistics.Nodes.Add(cubeProperty.Name + ": " + (cubeProperty.Value!=null ? cubeProperty.Value.ToString():""));
                        }
                        #endregion

                        //Dimensions
                        #region Dimensions
                        foreach (adoMdClient.Dimension cubeDimension in cube.Dimensions)
                        {
                            currentCubeDimensions.Nodes.Add(cubeDimension.Name);
                        }
                        #endregion

                        //Measures
                        #region Measures
                        foreach (adoMdClient.Measure cubeMeasure in cube.Measures)
                        {
                            TreeNode currentCubeMeasure = currentCubeMeasures.Nodes.Add(cubeMeasure.Name);
                            currentCubeMeasure.Nodes.Add("Caption: " + cubeMeasure.Caption);
                            currentCubeMeasure.Nodes.Add("Description: " + cubeMeasure.Description);
                            currentCubeMeasure.Nodes.Add("Display Folder: " + cubeMeasure.DisplayFolder);
                            currentCubeMeasure.Nodes.Add("Expression: " + cubeMeasure.Expression);
                            currentCubeMeasure.Nodes.Add("Numeric Precision: " + cubeMeasure.NumericPrecision);
                            currentCubeMeasure.Nodes.Add("Numeric Scale: " + cubeMeasure.NumericScale);
                            currentCubeMeasure.Nodes.Add("Units: " + cubeMeasure.Units);



                            foreach (adoMdClient.Property currentMeasureProperty in cubeMeasure.Properties)
                            {
                                currentCubeMeasure.Nodes.Add(currentMeasureProperty.Name + ": " + (currentMeasureProperty.Value!=null? currentMeasureProperty.Value.ToString():""));
                            }
                        }
                        #endregion

                        //KPIs
                        #region KPIs
                        foreach (adoMdClient.Kpi currentCubeKPI in cube.Kpis)
                        {
                            TreeNode currentCubeKPINode = currentCubeKPIs.Nodes.Add(currentCubeKPI.Name);
                            currentCubeKPINode.Nodes.Add("Caption: " + currentCubeKPI.Caption);
                            currentCubeKPINode.Nodes.Add("Description: " + currentCubeKPI.Description);
                            currentCubeKPINode.Nodes.Add("Display Folder: " + currentCubeKPI.DisplayFolder);
                            currentCubeKPINode.Nodes.Add("Status Graphic: " + currentCubeKPI.StatusGraphic);
                            currentCubeKPINode.Nodes.Add("Trend Graphic: " + currentCubeKPI.TrendGraphic);

                            /* Obtain KPI values (Value, Goal, Status, Trend)*/
                            //StringBuilder cmdKPIStatement = new StringBuilder("Select { ");
                            //cmdKPIStatement.Append(string.Concat("KPIValue(\"", currentCubeKPI.Name, "\"), "));
                            //cmdKPIStatement.Append(string.Concat("KPIGoal(\"", currentCubeKPI.Name, "\"), "));
                            //cmdKPIStatement.Append(string.Concat("KPIStatus(\"", currentCubeKPI.Name, "\"), "));
                            //cmdKPIStatement.Append(string.Concat("KPITrend(\"", currentCubeKPI.Name, "\") "));
                            //cmdKPIStatement.Append("} ON COLUMNS FROM [");
                            //cmdKPIStatement.Append(cube.Name);
                            //cmdKPIStatement.Append("]");


                            //adoMdClient.AdomdCommand cmdKPI = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand();
                            //cmdKPI.CommandText = cmdKPIStatement.ToString();
                            //cmdKPI.Connection = cnxAdomd;
                            //adoMdClient.CellSet adomdKPIValues = cmdKPI.ExecuteCellSet();


                            //foreach (adoMdClient.Cell currentCell in adomdKPIValues.Cells)
                            //{
                            //    string cellValue = currentCell.Value.ToString();
                            //    string cellFormattedValue = currentCell.FormattedValue;
                            //    int cellOrdinal = int.Parse(currentCell.CellProperties["CellOrdinal"].Value.ToString());
                            //    string cellHeader =  "Caption: " + adomdKPIValues.Axes[0].Positions[cellOrdinal].Members[0].Caption
                            //                       + "\n\rDescription: " + adomdKPIValues.Axes[0].Positions[cellOrdinal].Members[0].Description
                            //                       + "\n\rLevelName: " + adomdKPIValues.Axes[0].Positions[cellOrdinal].Members[0].LevelName
                            //                       + "\n\rName: " + adomdKPIValues.Axes[0].Positions[cellOrdinal].Members[0].Name
                            //                       + "\n\rUniqueName: " + adomdKPIValues.Axes[0].Positions[cellOrdinal].Members[0].UniqueName;
                            //    Debug.WriteLine("Cell Value           : " + cellValue);
                            //    Debug.WriteLine("Cell Formatted Value : " + cellFormattedValue);
                            //    Debug.WriteLine("Cell Ordinal         : " + cellOrdinal.ToString());
                            //    Debug.WriteLine("Cell Header          : " + cellHeader);
                            //}
                            

                            
                            foreach (adoMdClient.Property currentKPIProperty in currentCubeKPI.Properties)
                            {
                                adoMdClient.AdomdCommand cmdKPI = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand();
                                adoMdClient.CellSet adomdKPIValues;
                                String cellFormattedValue = "";
                                StringBuilder cmdKPIStatement = new StringBuilder("Select { ");
                                StringBuilder propertyName = new StringBuilder(currentKPIProperty.Name);
                                propertyName.Append(": ");
                                switch (currentKPIProperty.Name)
                                {
                                    case "KPI_VALUE":
                                        cmdKPIStatement.Append(string.Concat("KPIValue(\"", currentCubeKPI.Name, "\") "));
                                        cmdKPIStatement.Append("} ON COLUMNS FROM [");
                                        cmdKPIStatement.Append(cube.Name);
                                        cmdKPIStatement.Append("]");
                                        cmdKPI.CommandText = cmdKPIStatement.ToString();
                                        cmdKPI.Connection = cnxAdomd;
                                        adomdKPIValues = cmdKPI.ExecuteCellSet();
                                        if (adomdKPIValues.Cells.Count > 0)
                                        {
                                            cellFormattedValue = adomdKPIValues.Cells[0].FormattedValue;
                                        }
                                        propertyName.Append(currentKPIProperty.Value != null ? string.Concat(currentKPIProperty.Value.ToString(), " = ", cellFormattedValue) : "");
                                        break;

                                    case "KPI_GOAL":
                                        cmdKPIStatement.Append(string.Concat("KPIGoal(\"", currentCubeKPI.Name, "\") "));
                                        cmdKPIStatement.Append("} ON COLUMNS FROM [");
                                        cmdKPIStatement.Append(cube.Name);
                                        cmdKPIStatement.Append("]");
                                        cmdKPI.CommandText = cmdKPIStatement.ToString();
                                        cmdKPI.Connection = cnxAdomd;
                                        adomdKPIValues = cmdKPI.ExecuteCellSet();
                                        if (adomdKPIValues.Cells.Count > 0)
                                        {
                                            cellFormattedValue = adomdKPIValues.Cells[0].FormattedValue;
                                        }

                                        propertyName.Append(currentKPIProperty.Value != null ? string.Concat(currentKPIProperty.Value.ToString(), " = ", cellFormattedValue) : "");
                                        break;

                                    case "KPI_STATUS":
                                        cmdKPIStatement.Append(string.Concat("KPIStatus(\"", currentCubeKPI.Name, "\") "));
                                        cmdKPIStatement.Append("} ON COLUMNS FROM [");
                                        cmdKPIStatement.Append(cube.Name);
                                        cmdKPIStatement.Append("]");
                                        cmdKPI.CommandText = cmdKPIStatement.ToString();
                                        cmdKPI.Connection = cnxAdomd;
                                        adomdKPIValues = cmdKPI.ExecuteCellSet();
                                        if (adomdKPIValues.Cells.Count > 0)
                                        {
                                            cellFormattedValue = adomdKPIValues.Cells[0].FormattedValue;
                                            //Assign Traffic Light color to KPInode
                                            //Status should be a closed range [-1, 1]
                                            //Green range [0.34, 1]
                                            //Yellow color [-0.34, 0,34)
                                            //Red color [-1, -0,34)
                                            double status = double.Parse( adomdKPIValues.Cells[0].Value.ToString());
                                            if (status>=0.34)           //Green
                                            {
                                                currentCubeKPINode.BackColor=Color.Green;
                                            }
                                            else
                                            {
                                                if (status >= -0.34)    //Yellow
                                                {
                                                    currentCubeKPINode.BackColor = Color.Yellow;
                                                }
                                                else                   //Red
                                                {
                                                    currentCubeKPINode.BackColor = Color.Red;
                                                }
                                            }

                                        }

                                        propertyName.Append(currentKPIProperty.Value != null ? string.Concat(currentKPIProperty.Value.ToString(), " = ", cellFormattedValue) : "");
                                        //Assign Traffic Light color to KPInode
                                        //Status should be a closed range [-1, 1]
                                        //Green range [0.34, 1]
                                        //Yellow color [-0.34, 0,34)
                                        //Red color [-1, -0,34)
                                        
                                        break;

                                    case "KPI_TREND":
                                        cmdKPIStatement.Append(string.Concat("KPITrend(\"", currentCubeKPI.Name, "\") "));
                                        cmdKPIStatement.Append("} ON COLUMNS FROM [");
                                        cmdKPIStatement.Append(cube.Name);
                                        cmdKPIStatement.Append("]");
                                        cmdKPI.CommandText = cmdKPIStatement.ToString();
                                        cmdKPI.Connection = cnxAdomd;
                                        adomdKPIValues = cmdKPI.ExecuteCellSet();
                                        if (adomdKPIValues.Cells.Count > 0)
                                        {
                                            cellFormattedValue = adomdKPIValues.Cells[0].FormattedValue;
                                        }

                                        propertyName.Append(currentKPIProperty.Value != null ? string.Concat(currentKPIProperty.Value.ToString(), " = ", cellFormattedValue) : "");
                                        break;

                                    default:
                                        propertyName.Append(currentKPIProperty.Value != null ? currentKPIProperty.Value.ToString() : "");
                                        break;
                                }
                                currentCubeKPINode.Nodes.Add(propertyName.ToString());
                            }

                        }
                        #endregion

                        break;

                    case Microsoft.AnalysisServices.AdomdClient.CubeType.Dimension:
                        //Because dimensions are listed as part of the cubes collection you have to remove the leading '$' from the name
                        //Dimension names begin with a '$' in the cubes collection in ADOMD.Net
                        TreeNode dimensionRootNode = currentDatabaseDimensions.Nodes.Add(cube.Name.Substring(1));
                        foreach (adoMdClient.Dimension innerDimension in cube.Dimensions)
                        {
                            TreeNode innerDimensionNode = dimensionRootNode.Nodes.Add(innerDimension.UniqueName);
                            if (innerDimension.AttributeHierarchies.Count > 0)
                            {
                                TreeNode innerAttributeHierarchiesNode = innerDimensionNode.Nodes.Add("Attribute Hierarchies");
                                foreach (adoMdClient.Hierarchy attributeHierarchy in innerDimension.AttributeHierarchies)
                                {
                                    innerAttributeHierarchiesNode.Nodes.Add(attributeHierarchy.Name);
                                }
                            }
                            if (innerDimension.Hierarchies.Count > 0)
                            {
                                TreeNode innerHierarchiesNode = innerDimensionNode.Nodes.Add("Hierarchies");
                                foreach (adoMdClient.Hierarchy attributeHierarchy in innerDimension.Hierarchies)
                                {
                                    innerHierarchiesNode.Nodes.Add(attributeHierarchy.Name);
                                }
                            }

                        }
                        break;

                    default:
                        if (currentDatabaseUnknownType == null)
                        {
                            currentDatabaseUnknownType = currentDatabaseNode.Nodes.Add("UnknownType");
                        }
                        currentDatabaseUnknownType.Nodes.Add(string.Concat("[", cube.Type.ToString(), "] ", cube.Name));
                        break;
                }
            }

            //Assign first cube in the list as default value
            if (fromCube.Items.Count > 0)
            {
                fromCube.SelectedIndex = 0;
            }

            foreach (adoMdClient.MiningStructure miningStructure in databaseConnection.MiningStructures)
            {
                TreeNode currentMiningStructureNode = currentDatabaseMiningStructures.Nodes.Add(miningStructure.Name);
                TreeNode miningStructureColumnsNode = currentMiningStructureNode.Nodes.Add("Columns");
                TreeNode miningStructureModelsNode = currentMiningStructureNode.Nodes.Add("Models");

                #region MiningStructure Columns
                foreach (adoMdClient.MiningStructureColumn miningColumn in miningStructure.Columns)
                {
                    TreeNode columnNode = miningStructureColumnsNode.Nodes.Add(miningColumn.UniqueName);
                    columnNode.Nodes.Add("Description: " + miningColumn.Description);
                    
                    if (miningColumn.Columns.Count > 2)
                    {
                        StringBuilder columnsNames = new StringBuilder(miningColumn.Columns[0].UniqueName);
                        for (int i = 1; i < miningColumn.Columns.Count; i++)
                        {
                            columnsNames.Append(", ");
                            columnsNames.Append(miningColumn.Columns[i].UniqueName);
                        }
                        columnNode.Nodes.Add("Nested Columns: " + columnsNames);
                    }
                    else
                    {
                        if (miningColumn.Columns.Count == 1)
                        {
                            columnNode.Nodes.Add("Nested Columns: " + miningColumn.Columns[0].UniqueName);
                        }
                        else
                        {
                            columnNode.Nodes.Add("No Nested Columns!");
                        }

                    }
                }
                #endregion

                #region MiningStructure Models
                foreach (adoMdClient.MiningModel miningModel in miningStructure.MiningModels)
                {
                    TreeNode miningNode = miningStructureModelsNode.Nodes.Add(miningModel.Name);
                    miningNode.Nodes.Add("Algorithm: " + miningModel.Algorithm);
                    miningNode.Nodes.Add("Description: " + miningModel.Description);
                    miningNode.Nodes.Add("Is processed: " + miningModel.IsProcessed.ToString());
                    TreeNode columnsNode = miningNode.Nodes.Add("Columns");
                    foreach (adoMdClient.MiningModelColumn miningColumn in miningModel.Columns)
                    {
                        columnsNode.Nodes.Add(string.Concat(miningColumn.UniqueName, ": ", (miningColumn.IsInput ? "Input, " : ""), (miningColumn.IsPredictable ? "Predictable, " : ""), (miningColumn.IsTable ? "is Table" : "")));
                    }
                }
                #endregion

            }
            availableObjects.Enabled = true;
            actionTabs.Enabled = true;
            this.Cursor = Cursors.Arrow;
        }

        private void availableDatabases_SelectedIndexChanged(object sender, EventArgs e)
        {
            string selectedDatabase = ((ListBox)sender).SelectedItem.ToString();
            cnxAdomd.ChangeDatabase(selectedDatabase);
            SelectedDatabase = cnxAdomd.Database;
            databaseName.Text = SelectedDatabase;
            fromCube.Items.Clear();

            fillTreeView(availableObjects, cnxAdomd);
        }

        private void availableObjects_AfterSelect(object sender, TreeViewEventArgs e)
        {
            TreeNode selectedNode = ((TreeView)sender).SelectedNode;
            switch (selectedNode.Level)
            {
                case 0:
                    //MessageBox.Show("Database Level!! database is " + selectedNode.Text);
                    break;

                case 1:
                    //MessageBox.Show("Main object level!! object is " + selectedNode.Text);

                    break;

                default:
                    //MessageBox.Show("Children level!! child name is " + selectedNode.Text);

                    string parentName = selectedNode.Parent.Text;
                    switch (parentName)
                    {
                        case "Dimensions":
                            loadDimensionBrowser(cnxAdomd.Cubes[string.Concat("$", selectedNode.Text)].Dimensions[selectedNode.Text]);
                            break;

                        case "Cubes":
                            fromCube.SelectedIndex = fromCube.Items.IndexOf(selectedNode.Text);
                            break;

                        case "Models":
                            loadModelViewer(cnxAdomd.MiningModels[selectedNode.Text]);
                            break;

                        default:

                            break;
                    }

                    break;
            }
        }

        private void loadDimensionBrowser(adoMdClient.Dimension dimension)
        {
            dimensionBrowserfrm dimensionWindow = new dimensionBrowserfrm();
            dimensionWindow.Visible = false;
            dimensionWindow.Text = string.Concat("Dimension browser for ", dimension.Name);
            dimensionWindow.DimensionRoot = dimension;
            dimensionWindow.Visible = true;
            dimensionWindow.Activate();
            dimensionWindow.TopMost = true;

        }


        private void loadModelViewer(adoMdClient.MiningModel miningModel)
        {
            MiningModelViewerControl modelCx = null;
            adoMdClient.MiningService miningService = cnxAdomd.MiningServices[miningModel.Algorithm];

            switch (miningService.ViewerType)
            {
                case "Microsoft_AssociationRules_Viewer":
                    modelCx = new AssociationViewer();
                    break;

                case "Microsoft_Cluster_Viewer":
                    modelCx = new ClusterViewer();
                    break;

                case "Microsoft_NaiveBayesian_Viewer":
                    modelCx = new NaiveBayesViewer();
                    break;

                case "Microsoft_NeuralNetwork_Viewer":
                    modelCx = new NeuralNetViewer();
                    break;

                case "Microsoft_SequenceCluster_Viewer":
                    modelCx = new SequenceClusterViewer();
                    break;

                case "Microsoft_TimeSeries_Viewer":
                    modelCx = new TimeSeriesViewer();
                    break;

                case "Microsoft_Tree_Viewer":
                    modelCx = new TreeViewer();
                    break;

                default:
                    MessageBox.Show("User defined viewers as [" + miningService.ViewerType + "] are not supported here", "Simple Sample ADOMD ClientAccess", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                    break;
            }

            modelCx.ConnectionString = string.Concat("Provider=MSOLAP.3;",cnxAdomd.ConnectionString,";Initial Catalog=" +cnxAdomd.Database);
            modelCx.MiningModelName = miningModel.Name;
            modelCx.Dock = DockStyle.Fill;

            miningModelViewerfrm viewerWindow = new miningModelViewerfrm();
            viewerWindow.Visible = false;
            viewerWindow.Text = string.Concat("Data Mining Model View of ", miningModel.Parent.Name, ".", miningModel.Name);
            viewerWindow.Controls["viewerArea"].Controls.Add(modelCx);
            
            modelCx.LoadViewerData(null);
            viewerWindow.Visible = true;
            viewerWindow.Activate();
            viewerWindow.TopMost = true;

        }
    }
}